USE [HRSmart_TEST]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Phyo
-- Create date: 28 Oct 2014 1:57 PM
-- Description:	Generate Dataset for EPE
-- =============================================
/*
Declare @StartDate datetime, @EndDate datetime
SET @StartDate = '01-Oct-2014 00:00 AM' 
SET @EndDate = '30-Oct-2014 11:59:59.999 AM' 
EXEC [sprocCandidateEPESelectDataSet] @StartDate = @StartDate, @EndDate = @EndDate
*/
ALTER PROCEDURE [dbo].[sprocCandidateEPESelectDataSet]
@StartDate datetime,
@EndDate datetime
AS
BEGIN

	--Family
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		FR.Relationship,
		CASE WHEN LEN(FR.NRICFIN) > 0 THEN FR.NRICFIN 
		ELSE FR.PassportNo END As [Family Member Identity No],
		ID.IDType As [Family Member Identity Type],
		FR.FullName As [Family Member Name],
		CONVERT(VARCHAR(10),FR.DOB,110) As [Family Member Date of Birth],
		CASE FR.Gender
			WHEN 'M' THEN 1
			WHEN 'F' THEN 0
			ELSE '-' END As [Family Member Gender],
		'' As [Residence Status],
		'' As [Contact Order],
		'' As [Family Member Contact 1],
		'' As [Family Member Contact 2],
		'' As [Family Member Occupation],
		[Address] As [Family Member Address],
		'' As [Family Member Country],
		'' As [Family Member State],
		'' As [Family Member City],
		Postcode As [Family Member Postal Code]
	FROM tblEmpPersonal EP
	INNER JOIN tblEmpFR FR ON EP.CandidateID = FR.CandidateID
	INNER JOIN tblCandidate C ON EP.CandidateID = C.CandidateID
	LEFT OUTER JOIN tblIDTypeInfo ID ON FR.IDTypeID = ID.ID
	Where EP.CreatedDate Between @StartDate And @EndDate
	Order By C.FullName


	--Address
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		'ePortal' As [Address Contact Location],
		EP.AddressLocal As [Address Line 1],
		'' As [Address Line 2],
		'' As [Address Line 3],
		'' As [Address Country],
		'' As [Address State],
		'' As [Address City],
		EP.PostalCode As [Address Postal Code],
		'1' As [Is Mailing Address]
	FROM tblEmpPersonal EP
	INNER JOIN tblCandidate C ON EP.CandidateID = C.CandidateID
	WHERE LEN(EP.AddressLocal) > 0 And EP.CreatedDate Between @StartDate And @EndDate
	UNION
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		'Home' As [Address Contact Location],
		EP.OAddress As [Address Line 1],
		'' As [Address Line 2],
		'' As [Address Line 3],
		'' As [Address Country],
		'' As [Address State],
		'' As [Address City],
		'' As [Address Postal Code],
		'0' As [Is Mailing Address]
	FROM tblEmpPersonal EP
	INNER JOIN tblCandidate C ON EP.CandidateID = C.CandidateID
	WHERE LEN(EP.OAddress) > 0 And EP.CreatedDate Between @StartDate And @EndDate
	Order By C.FullName

	--Contact
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		'ePortal' As [Contact Location],
		C.ContactNo As [Contact No.]
	FROM tblCandidate C
	INNER JOIN tblEmpPersonal EP ON C.CandidateID = EP.CandidateID
	Where LEN(C.ContactNo) > 0 And EP.CreatedDate Between @StartDate And @EndDate
	UNION
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		'Others' As [Contact Location],
		C.ContactNo As [Contact No.]
	FROM tblCandidate C
	INNER JOIN tblEmpPersonal EP ON C.CandidateID = EP.CandidateID
	Where LEN(C.AltContactNo) > 0 And EP.CreatedDate Between @StartDate And @EndDate
	Order By C.FullName

	--Education
	Select
		CASE WHEN LEN(C.NRICFIN) > 0 THEN C.NRICFIN 
		ELSE C.PassportNo END IdentityNo,
		'' As [Employee ID],
		C.FullName As [Personal Name],
		EL.EduLevel As [Education Level],
		CASE EE.EduLevelID 
		WHEN 5 THEN
			EE.Qualification
		ELSE EQ.Qualification END [Education ID],
		EE.Institutional As [Insitution Name],
		'' As [Start Date],
		'' As [End Date],
		'1' As [Highest Education]
	FROM tblEmpEdu EE
	INNER JOIN tblEmpPersonal EP ON EE.CandidateID = EP.CandidateID
	INNER JOIN tblCandidate C ON EP.CandidateID = C.CandidateID
	INNER JOIN tblEduLevelInfo EL ON EE.EduLevelID = EL.ID
	INNER JOIN tblEduQualificationInfo EQ ON EE.QualificationID = EQ.ID
	Where EP.CreatedDate Between @StartDate And @EndDate
	Order By C.FullName

	--Personnel
	Select 
		'' As [Emp. ID],
		C.Salutation As Title,
		C.FullName As [Personal Name],
		EP.NameAlias As [Alias],
		N.Country As Nationality,
		C.NRICFIN As IdentityNo,
		C.PassportNo As [Other ID],
		ID.IDType As [Identity Type],
		R.Religion,
		MS.MaritalStatus,
		RC.Race,
		CASE C.Gender
			WHEN 'M' THEN 1
			WHEN 'F' THEN 0
			ELSE '-' END As [Family Member Gender],
		CONVERT(VARCHAR(10),C.DOB,110) As [Date Of Birth],
		N.Country As [Country of Birth],
		'' As [Residence Status],
		'' As [Resident Status Effective Date],
		'' As [Residence Status Remarks],
		C.Email As [Email Address]
	FROM tblEmpPersonal EP
	INNER JOIN tblCandidate C ON EP.CandidateID = C.CandidateID
	INNER JOIN tblNationalityInfo N ON C.NationalityID = N.ID
	INNER JOIN tblIDTypeInfo ID ON C.IDTypeID = ID.ID
	INNER JOIN tblReligionInfo R ON EP.ReligionID = R.ID
	INNER JOIN tblMaritalStatusInfo MS ON EP.MaritalStatusID = MS.ID
	INNER JOIN tblRaceInfo RC ON C.RaceID = RC.ID
	Where EP.CreatedDate Between @StartDate And @EndDate
	Order By C.FullName

END